package red.service.impl;

import net.sf.json.JSONArray;
import red.action.PageAction;
import red.model.Column;
import red.model.Text;
import red.model.User;
import red.service.PagingService;
import red.util.MysqlConnectUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PagingServiceImpl implements PagingService {
    private MysqlConnectUtils mysqlConnectUtils = new MysqlConnectUtils();
    private java.sql.PreparedStatement preparedStatement;
    private ResultSet rs;
    private ResultSet resultSet;
    private String sql;

    //用户查询
    public List allUser(){
        MysqlConnectUtils conn =new MysqlConnectUtils();
        List list = new ArrayList();
        String sql = "select * from user";
        try {
            conn.connection();
            preparedStatement = conn.conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                User recommendBean = new User();
                recommendBean.setUser_name(rs.getString("user_name"));
                String Date=rs.getString("logintime");
                Date=Date.substring( 0,16 );
                recommendBean.setLogintime(Date);
                String sql1 = "select count(*) from article where user_name = '"+recommendBean.getUser_name()+"'";
                try{
                    conn.connection();
                    preparedStatement = conn.conn.prepareStatement(sql1);
                    resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        recommendBean.setNumber(resultSet.getInt(1));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                list.add(recommendBean);
            }
            JSONArray jsonObject= JSONArray.fromObject(list);
            if (jsonObject.size()>0) {
                PageAction pagingActionlist=new PageAction();
                return jsonObject;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return null;
    }


    //用户分页查询
    public List pageUser(String pageSize ,String page) {
        MysqlConnectUtils conn =new MysqlConnectUtils();
        List list = new ArrayList();
        int start = Integer.parseInt(page);
        int count = Integer.parseInt(pageSize);
        String sql = "select * from user limit " + (start-1)*count+ "," + count + "";
        try {
            conn.connection();
            preparedStatement = conn.conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                User recommendBean = new User();
                recommendBean.setUser_name(rs.getString("user_name"));
                String Date=rs.getString("logintime");
                Date=Date.substring( 0,16 );
                recommendBean.setLogintime(Date);
                String sql1 = "select count(*) from article where user_name = '"+recommendBean.getUser_name()+"'";
                try{
                    conn.connection();
                    preparedStatement = conn.conn.prepareStatement(sql1);
                    resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        recommendBean.setNumber(resultSet.getInt(1));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                list.add(recommendBean);
            }
            JSONArray jsonObject= JSONArray.fromObject(list);
            if (jsonObject.size()>0) {
                PageAction pagingActionlist=new PageAction();
                return jsonObject;
            }else {
                return jsonObject;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }



    //栏目查询
    @Override
    public List allColumn() {
        MysqlConnectUtils conn =new MysqlConnectUtils();
        List list = new ArrayList();
        String sql = "select * from sys_column";
        try {
            conn.connection();
            preparedStatement = conn.conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Column recommendBean = new Column();
                recommendBean.setColumn_name(rs.getString("column_name"));
                String Date=rs.getString("column_date");
                Date=Date.substring( 0,16 );
                recommendBean.setColumn_date(Date);
                String sql1 = "select count(*) from article where column_name = '"+recommendBean.getColumn_name()+"'";
                try{
                    conn.connection();
                    preparedStatement = conn.conn.prepareStatement(sql1);
                    resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        recommendBean.setNumber(resultSet.getInt(1));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                list.add(recommendBean);
            }
            JSONArray jsonObject= JSONArray.fromObject(list);
            if (jsonObject.size()>0) {
                PageAction pagingActionlist=new PageAction();
                return jsonObject;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



    //栏目分页查询
    @Override
    public List pageColumn(String pageSize ,String page) {
        MysqlConnectUtils conn =new MysqlConnectUtils();
        List list = new ArrayList();
        int start = Integer.parseInt(page);
        int count = Integer.parseInt(pageSize);
        String sql = "select * from sys_column limit "+(start-1)*count+ "," + count + "";
        try {
            conn.connection();
            preparedStatement = conn.conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Column recommendBean = new Column();
                recommendBean.setColumn_name(rs.getString("column_name"));
                String Date=rs.getString("column_date");
                Date=Date.substring( 0,16 );
                recommendBean.setColumn_date(Date);
                String sql1 = "select count(*) from article where column_name = '"+recommendBean.getColumn_name()+"'";
                try{
                    conn.connection();
                    preparedStatement = conn.conn.prepareStatement(sql1);
                    resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        recommendBean.setNumber(resultSet.getInt(1));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                list.add(recommendBean);
            }
            JSONArray jsonObject= JSONArray.fromObject(list);
            if (jsonObject.size()>0) {
                PageAction pagingActionlist=new PageAction();
                return jsonObject;
            }else {
                return jsonObject;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    //文章查询
    @Override
    public List allText() {
        MysqlConnectUtils conn =new MysqlConnectUtils();
        List list = new ArrayList();
        String sql = "select * from article";
        try {
            conn.connection();
            preparedStatement = conn.conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Text recommendBean = new Text();
                recommendBean.setHeadline(rs.getString("headline"));
                String Date=rs.getString("publish_date");
                Date=Date.substring( 0,16 );
                recommendBean.setPublish_date(Date);
                recommendBean.setAudit(rs.getString("audit"));
                recommendBean.setPage(rs.getString("page"));
                recommendBean.setLike_number(rs.getString("like_number"));
                list.add(recommendBean);
            }
            JSONArray jsonObject= JSONArray.fromObject(list);
            if (jsonObject.size()>0) {
                PageAction pagingActionlist=new PageAction();
                return jsonObject;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    //文章分页查询
    @Override
    public List pageText(String pageSize, String page) {
        MysqlConnectUtils conn =new MysqlConnectUtils();
        List list = new ArrayList();
        int start = Integer.parseInt(page);
        int count = Integer.parseInt(pageSize);
        String sql = "select * from article limit "+(start-1)*count+ "," + count + "";
        try {
            conn.connection();
            preparedStatement = conn.conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Text recommendBean = new Text();
                recommendBean.setHeadline(rs.getString("headline"));
                String Date=rs.getString("publish_date");
                Date=Date.substring( 0,16 );
                recommendBean.setPublish_date(Date);
                recommendBean.setAudit(rs.getString("audit"));
                recommendBean.setPage(rs.getString("page"));
                recommendBean.setLike_number(rs.getString("like_number"));
                list.add(recommendBean);
            }
            JSONArray jsonObject= JSONArray.fromObject(list);
            if (jsonObject.size()>0) {
                PageAction pagingActionlist=new PageAction();
                return jsonObject;
            }else {
                return jsonObject;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



    //查询总数
    public int getTotal(int name) {
        MysqlConnectUtils conn =new MysqlConnectUtils();
        int total = 0;
        if (name==1){
            sql = "select count(*) from user";
        }
        if (name==2){
            sql = "select count(*) from sys_column";
        }
        if (name == 3) {
            sql = "select count(*) from article";
        }
        try{
            conn.connection();
            preparedStatement = conn.conn.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return total;
    }

}
